package com.fsbm.ctech.maxis.ebpsbackend;

import java.io.*;
import java.text.*;
import java.util.*;
import java.util.Date;
import java.sql.*;
/**
 * Program to extract adhoc request for Bill Statement Delivery. Reads 'custlog' table thru JDBC
 * APIs and passes statementID and Email Address in this notation - stmtId~emailaddress. It returns
 * a set whose activity code is AC007. It filters record with empty userfield1 and userfield2.
 * <p>
 * Maxis Backend Program - Extract Statement Id and Email Address from Adhoc Request pool.
 *
 * @author Michael Ty Chan
 */
public class GetAdhoc {
    static ConfigObject cfgObj = new ConfigObject();
    public static Vector retrieveStatementIds() throws SQLException {
        Vector adhocStatements = new Vector();
        String url                = cfgObj.databaseSourceName;
        ResultSet rs              = null;
        PreparedStatement ps      = null;
        Connection con            = null;
        Statement stmt            = null;
        String concatValue        = "";

        try {
            Class.forName(cfgObj.databaseDriver);
        } catch(java.lang.ClassNotFoundException e) {
            System.err.print("ClassNotFoundException: ");
            System.err.println(e.getMessage());
        }

        try {
            con            = DriverManager.getConnection(url, cfgObj.databaseUserID, cfgObj.databasePassword);
            con.setAutoCommit(false);
            stmt           = con.createStatement();
            // uncomment next line for oracle
            ResultSet uprs = stmt.executeQuery("SELECT userfield1,userfield2 FROM custlog WHERE UserField1<>' ' AND userfield2<>' ' AND activitycode='AC007' AND (UserField4 is null OR UserField4 = '')");
            // ResultSet uprs = stmt.executeQuery("SELECT userfield1,userfield2 FROM custlog WHERE UserField1<>trim(userfield1) AND userfield2<> trim(userfield2) AND activitycode='AC007' AND UserField4 is null");
            // uncomment next line for sql server
            //ResultSet uprs = stmt.executeQuery("SELECT userfield1, userfield2 FROM custlog WHERE (ActivityCode = 'AC007')");

            while (uprs.next()) {
                String stmtId     = uprs.getString("userfield1");
                if (!stmtId.equals("")) {
                    String emailAdd   = uprs.getString("userfield2");
                    concatValue       = stmtId.trim() + "~" + emailAdd.trim();
                    adhocStatements.addElement(concatValue.toString());
                } else {
                    cfgObj.write("Process Name(retrieveStatementIds)- Some request are invalid for Bill Statement Delivery");
                }
            }
            uprs.close();
            stmt.close();

            // the ffg lines are temporary and needs to be incorporated inside the while loop on top to balance extracted
            con.setAutoCommit(false);
            stmt           = con.createStatement();

            String sqlStmt = "UPDATE custlog SET UserField4='Extracted' WHERE UserField1<>' ' AND UserField2<>' ' AND ActivityCode='AC007'";
            //String sqlStmt = "UPDATE custlog SET UserField4='Extracted' WHERE UserField1<>trim(userfield1) AND userfield2<> trim(userfield2) AND activitycode='AC007' AND UserField4 is null";

            stmt.executeUpdate(sqlStmt);
            con.commit();
            con.setAutoCommit(true);
            stmt.close();
            con.close();

        } catch(SQLException ex) {
            cfgObj.write("Process Name(retrieveStatementIds)- SQLSTATE: " + ex.getSQLState() +
            " Msg: " + ex.getMessage() + " ErrCode: " + ex.getErrorCode(), "AdhocErr");
        } catch (Exception ex) {
            cfgObj.write("Process Name(retrieveStatementIds)- " + ex.getClass() + " " + ex.getMessage(), "AdhocErr");
        }
        return adhocStatements;
    }
}

